{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"item_title\": \"6 bitcoin ransomware easy money\", \"vendor_name\": \"topnotchmoneymaker     (3950) (4.75\\u00a0\\u00a0\\u00a0\\u00a0) (2819/115/98)\", \"shipping_details\": \"worldwide\", \"item_description\": \"6 bitcoin ransomware \\u27770\\u27767 - next 10 orders pay only $8 then prices go back up to $95description:ransomware is a type of malware that prevents or limits users from accessing their system, either by locking the system's screen or by locking the users' files unless a ransom is paid. more modern ransomware families, collectively categorized as crypto-ransomware, encrypt certain file types on infected systems and forces users to pay the ransom through certain online payment methods to get a decrypt key.\\u25b6 i will provide you with-- 6 ---urls to source codes, with full instructions on customizing them, and 4 original distribution methods.\\u25b6 simply edit it and set your own price, email, extensions, message, timer etc.   ransomware is easy to build and there is no reason why you should pay hundreds for one that you can easily customize yourself. \\u25b6 you'll receive 4 unique \\u2605no email\\u2605 fool-proof methods with better than 90% percent success.  \\u2655\\u2655 68 computers infected out of 72 distributions - over $214,400.00 received!! \\u2655\\u2655\\u25b6 act now and receive our widely-talked-about bonus pack - over 1,000 free guides on fraud, cashout, hacking, more...\\u2655\\u2655 next 10 orders pay only $8 then prices go back up to $95 \\u2655\\u2655\"}\n"
     ]
    }
   ],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import json\n",
    "import sqlalchemy\n",
    "\n",
    "#Read text file which is HTML\n",
    "#ToDo: take it from the mongoDB\n",
    "file = open('demo_html.txt', 'r') \n",
    "\n",
    "def sanitizeText(str):\n",
    "    return str.lower().replace('\\n', '').replace('\\t', '').strip()\n",
    "\n",
    "def sanitizeKey(str):\n",
    "    return str.lower().replace(' ', '_').strip()\n",
    "\n",
    "\n",
    "soup = BeautifulSoup(file)\n",
    "#Parsing Start\n",
    "lobbying = {}\n",
    "for element in details_parts:\n",
    "    lobbying[sanitizeKey(element.label.get_text())] = sanitizeText(element.span.get_text())\n",
    "lobbying['title'] = sanitizeText(soup.find('div', class_='title').get_text())\n",
    "lobbying['description'] = sanitizeText(soup.find(id=\"offerDescription\").get_text())\n",
    "#print json.dumps(lobbying)\n",
    "#Parsing End    \n",
    "\n",
    "result = {}\n",
    "result['item_title'] = lobbying['title']\n",
    "result['item_description'] = lobbying['description']\n",
    "result['vendor_name'] = lobbying['vendor']\n",
    "result['shipping_details'] = lobbying['ships_to']\n",
    "\n",
    "json_data = json.dumps(result)\n",
    "print json_data\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('sqlite:///db.sql', echo=True)\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "Base = declarative_base()\n",
    "\n",
    "from sqlalchemy import Column, Integer, String\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "     fullname = Column(String)\n",
    "     password = Column(String)\n",
    "     def __repr__(self):\n",
    "            return \"<User(name='%s', fullname='%s', password='%s')>\" % (self.name, self.fullname, self.password)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"category\": \"remote exploits\", \"description\": \"descriptionthis metasploit module leverages an unauthenticated credential disclosure vulnerability to execute arbitrary commands on dir-850l routers as an authenticated user.\", \"topic_author\": \"metasploit\", \"platform\": \"hardware\", \"post_content\": \"14-11-2017\", \"topic_content\": \"D-Link DIR-850L Unauthenticated Command Execution Exploit\"}\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "def sanitize(str):\n",
    "    return str.lower().replace('\\n', '').replace('\\t', '').strip()\n",
    "\n",
    "data = json.load(open('data.json'))\n",
    "soup = BeautifulSoup(data['html'])\n",
    "\n",
    "result = {}\n",
    "content = soup.find('div', class_='exploit_view_table')\n",
    "content = content.find_all('div', class_='exploit_view_table_content') \n",
    "\n",
    "result['topic_content'] = content[0].find('a').get_text()\n",
    "result['post_content'] = content[1].find('a').get_text()\n",
    "result['category'] = content[2].find('a').get_text()\n",
    "result['platform'] = content[3].find('a').get_text()\n",
    "result['description'] = sanitize(content[8].get_text())\n",
    "\n",
    "content = soup.find('div', class_='exploit_view_table_user')\n",
    "content = content.find_all('div', class_='exploit_view_table_user_content') \n",
    "\n",
    "result['topic_author'] = sanitize(content[0].find('a').get_text())\n",
    "print json.dumps(result)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('sqlite:///db.sql', echo=True)\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "Base = declarative_base()\n",
    "\n",
    "from sqlalchemy import Column, Integer, String\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    fullname = Column(String)\n",
    "    password = Column(String)\n",
    "    def __repr__(self):\n",
    "        return \"<User(name='%s', fullname='%s', password='%s')>\" % (\n",
    "                                self.name, self.fullname, self.password)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bad = User()\n",
    "bad.name = \"shlomo\"\n",
    "bad.fullname = \"alkfhjasd as;kdj a\"\n",
    "bad.password = \"hello\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'shlomo'"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-11-23 18:48:24,036 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2017-11-23 18:48:24,037 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-23 18:48:24,040 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2017-11-23 18:48:24,041 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-23 18:48:24,044 INFO sqlalchemy.engine.base.Engine PRAGMA table_info(\"users\")\n",
      "2017-11-23 18:48:24,045 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-23 18:48:24,048 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE users (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tname VARCHAR, \n",
      "\tfullname VARCHAR, \n",
      "\tpassword VARCHAR, \n",
      "\tPRIMARY KEY (id)\n",
      ")\n",
      "\n",
      "\n",
      "2017-11-23 18:48:24,049 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-23 18:48:24,051 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')\n",
    "session.add(ed_user)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-11-23 19:02:32,481 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2017-11-23 19:02:32,484 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password \n",
      "FROM users ORDER BY users.id\n",
      "2017-11-23 19:02:32,486 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "for instance in session.query(User).order_by(User.id):\n",
    "    print(instance.name, instance.fullname)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-11-23 19:06:27,856 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password \n",
      "FROM users \n",
      "WHERE users.name = ?\n",
      " LIMIT ? OFFSET ?\n",
      "2017-11-23 19:06:27,858 INFO sqlalchemy.engine.base.Engine ('ed', 1, 0)\n"
     ]
    }
   ],
   "source": [
    "our_user = session.query(User).filter_by(name='ed').first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<User(name='ed', fullname='Ed Jones', password='edspassword')>"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "our_user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/sbadusa/projects/smackdown\n",
      "['.ipynb_checkpoints', 'db.sql', 'db.sql-journal', 'input files', 'Untitled.ipynb', 'Untitled1.ipynb']\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "cwd = os.getcwd()\n",
    "print cwd\n",
    "\n",
    "files = os.listdir(cwd)\n",
    "\n",
    "print files\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
